--    Author    : MAYANTING
--    Name      : ADM.RPT_SUST_DLMG_TERM_AMOUNT_DEPF.HQL
--    Functions : 表10：存款发生额分额度期限结构表
--    Purpose   :
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2018-06-12  MAYANTING           1.CREATE THE PROCEDURE
--
INSERT OVERWRITE TABLE ADM.RPT_SUST_DLMG_TERM_AMOUNT_DEPF PARTITION (DATA_DATE = '#V_DATA_DATE#')
SELECT  T.FIN_ORG_DIST
       ,T.FIN_ORG_NO
       ,T.CCY
       ,T.INTERVAL_CODE1                   -- 存款余额区间分类
       ,T.INTERVAL_DESC1                   -- 存款余额区间描述
       ,SUM(T.CURR_AMOUNT         )/100000000 -- 活期存款余额
       ,SUM(T.CURR_AMOUNT_WSUM    )/100000000 -- 活期存款余额加权值
       ,SUM(T.INFORM_AMOUNT       )/100000000 -- 通知存款余额
       ,SUM(T.INFORM_AMOUNT_WSUM  )/100000000 -- 通知存款余额加权值
       ,SUM(T.ENSURE_AMOUNT       )/100000000 -- 保证金存款余额
       ,SUM(T.ENSURE_AMOUNT_WSUM  )/100000000 -- 保证金存款余额加权值
       ,SUM(T.AGREE_AMOUNT        )/100000000 -- 协定存款余额
       ,SUM(T.AGREE_AMOUNT_WSUM   )/100000000 -- 协定存款余额加权值
       ,SUM(T.REG_CURR_AMOUNT     )/100000000 -- 定活两便存款余额
       ,SUM(T.REG_CURR_AMOUNT_WSUM)/100000000 -- 定活两便存款余额加权值
       ,SUM(T.AMOUNT_0_3M         )/100000000 -- 存款余额_3月以内
       ,SUM(T.WSUM_0_3M        )/100000000    -- 存款余额加权值_3月以内
       ,SUM(T.AMOUNT_3_6M         )/100000000 -- 存款余额_3到6个月
       ,SUM(T.WSUM_3_6M        )/100000000    -- 存款余额加权值_3到6个月
       ,SUM(T.AMOUNT_6_12M        )/100000000 -- 存款余额_6个月到1年
       ,SUM(T.WSUM_6_12M       )/100000000    -- 存款余额加权值_6个月到1年
       ,SUM(T.AMOUNT_1_3Y         )/100000000 -- 存款余额_1到3年
       ,SUM(T.WSUM_1_3Y        )/100000000    -- 存款余额加权值_1到3年
       ,SUM(T.AMOUNT_3_5Y         )/100000000 -- 存款余额_3到5年
       ,SUM(T.WSUM_3_5Y        )/100000000    -- 存款余额加权值_3到5年
       ,SUM(T.AMOUNT_5_999Y       )/100000000 -- 存款余额_5年以上
       ,SUM(T.WSUM_5_999Y      )/100000000    -- 存款余额加权值_5年以上
       ,SUM(T.AMOUNT_ALL          )/100000000 -- 存款余额_所有期限
       ,SUM(T.WSUM_ALL         )/100000000    -- 存款余额加权值_所有期限
FROM (
    -- 按金额区间
    SELECT
         AREA.AREA_CODE_4                                                                                          AS FIN_ORG_DIST
        ,DEP.FIN_ORG_NO                                                                                            AS FIN_ORG_NO
        ,DEP.CCY                                                                                                   AS CCY
        ,QJ.DEPT_INTERVAL_ID                                                                                       AS INTERVAL_CODE1       -- 存款余额区间分类
        ,QJ.DEPT_INTERVAL_DSCR                                                                                     AS INTERVAL_DESC1       -- 存款余额区间描述
        ,0                                                                                                         AS CURR_AMOUNT          -- 活期存款余额
        ,0                                                                                                         AS CURR_AMOUNT_WSUM     -- 活期存款余额加权值
        ,COALESCE(CASE WHEN DEP.DEPOSIT_TYPE='D03' THEN DEP.ACTUAL_AMOUNT ELSE 0 END,0)                            AS INFORM_AMOUNT        -- 通知存款余额
        ,COALESCE(CASE WHEN DEP.DEPOSIT_TYPE='D03' THEN DEP.WSUM_AMOUNT   ELSE 0 END,0)                            AS INFORM_AMOUNT_WSUM   -- 通知存款余额加权值
        ,COALESCE(CASE WHEN DEP.DEPOSIT_TYPE IN('D061','D062','D063','D064','D065','D066','D067','D068','D069')
                       THEN DEP.ACTUAL_AMOUNT ELSE 0 END,0)                                                        AS ENSURE_AMOUNT        -- 保证金存款余额
        ,COALESCE(CASE WHEN DEP.DEPOSIT_TYPE IN('D061','D062','D063','D064','D065','D066','D067','D068','D069')
                       THEN DEP.WSUM_AMOUNT   ELSE 0 END,0)                                                        AS ENSURE_AMOUNT_WSUM   -- 保证金存款余额加权值
        ,COALESCE(CASE WHEN DEP.DEPOSIT_TYPE IN ('D051','D052') THEN DEP.ACTUAL_AMOUNT ELSE 0 END,0)               AS AGREE_AMOUNT         -- 协定存款余额
        ,COALESCE(CASE WHEN DEP.DEPOSIT_TYPE IN ('D051','D052') THEN DEP.WSUM_AMOUNT   ELSE 0 END,0)               AS AGREE_AMOUNT_WSUM    -- 协定存款余额加权值
        ,COALESCE(CASE WHEN DEP.DEPOSIT_TYPE='D02' THEN DEP.ACTUAL_AMOUNT ELSE 0 END,0)                            AS REG_CURR_AMOUNT      -- 定活两便存款余额
        ,COALESCE(CASE WHEN DEP.DEPOSIT_TYPE='D02' THEN DEP.WSUM_AMOUNT   ELSE 0 END,0)                            AS REG_CURR_AMOUNT_WSUM -- 定活两便存款余额加权值
        ,COALESCE(CASE WHEN DEP.TERM_CODE IN ('1','2') THEN DEP.ACTUAL_AMOUNT ELSE 0 END,0)                        AS AMOUNT_0_3M          -- 存款余额_3月以内
        ,COALESCE(CASE WHEN DEP.TERM_CODE IN ('1','2') THEN DEP.WSUM_AMOUNT   ELSE 0 END,0)                        AS WSUM_0_3M            -- 存款余额加权值_3月以内
        ,COALESCE(CASE WHEN DEP.TERM_CODE ='3' THEN DEP.ACTUAL_AMOUNT ELSE 0 END,0)                                AS AMOUNT_3_6M          -- 存款余额_3到6个月
        ,COALESCE(CASE WHEN DEP.TERM_CODE ='3' THEN DEP.WSUM_AMOUNT   ELSE 0 END,0)                                AS WSUM_3_6M            -- 存款余额加权值_3到6个月
        ,COALESCE(CASE WHEN DEP.TERM_CODE ='4' THEN DEP.ACTUAL_AMOUNT ELSE 0 END,0)                                AS AMOUNT_6_12M         -- 存款余额_6个月到1年
        ,COALESCE(CASE WHEN DEP.TERM_CODE ='4' THEN DEP.WSUM_AMOUNT   ELSE 0 END,0)                                AS WSUM_6_12M           -- 存款余额加权值_6个月到1年
        ,COALESCE(CASE WHEN DEP.TERM_CODE IN ('5','6') THEN DEP.ACTUAL_AMOUNT ELSE 0 END,0)                        AS AMOUNT_1_3Y          -- 存款余额_1到3年
        ,COALESCE(CASE WHEN DEP.TERM_CODE IN ('5','6') THEN DEP.WSUM_AMOUNT   ELSE 0 END,0)                        AS WSUM_1_3Y            -- 存款余额加权值_1到3年
        ,COALESCE(CASE WHEN DEP.TERM_CODE ='7' THEN DEP.ACTUAL_AMOUNT ELSE 0 END,0)                                AS AMOUNT_3_5Y          -- 存款余额_3到5年
        ,COALESCE(CASE WHEN DEP.TERM_CODE ='7' THEN DEP.WSUM_AMOUNT   ELSE 0 END,0)                                AS WSUM_3_5Y            -- 存款余额加权值_3到5年
        ,COALESCE(CASE WHEN DEP.TERM_CODE IN ('8','9') THEN DEP.ACTUAL_AMOUNT ELSE 0 END,0)                        AS AMOUNT_5_999Y        -- 存款余额_5年以上
        ,COALESCE(CASE WHEN DEP.TERM_CODE IN ('8','9') THEN DEP.WSUM_AMOUNT   ELSE 0 END,0)                        AS WSUM_5_999Y          -- 存款余额加权值_5年以上
        ,COALESCE(DEP.ACTUAL_AMOUNT,0)                                                                             AS AMOUNT_ALL           -- 存款余额_所有期限
        ,COALESCE(DEP.WSUM_AMOUNT,0)                                                                               AS WSUM_ALL             -- 存款余额加权值_所有期限
    FROM (SELECT * FROM EDW.DS_DEPB_SUM WHERE DATA_DATE='#V_DATA_DATE#')DEP
    -- 与地区表最细级关联
    LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON DEP.FIN_ORG_NO=REAL.ORG_ID AND '#V_DATA_DATE#' BETWEEN REAL.START_DATE AND REAL.END_DATE
    LEFT JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '#V_DATA_DATE#' BETWEEN AREA.START_DATE AND AREA.END_DATE
    LEFT JOIN DIMENSION.DIM_DEPOSIT_AMOUNT_INTERVAL QJ ON DEP.INTERVAL_CODE2=QJ.DEPT_INTERVAL_ID AND '#V_DATA_DATE#' BETWEEN QJ.START_DATE AND QJ.END_DATE

    UNION ALL

    -- 合计
    SELECT
         AREA.AREA_CODE_4                                                                                          AS FIN_ORG_DIST
        ,DEP.FIN_ORG_NO                                                                                            AS FIN_ORG_NO
        ,DEP.CCY                                                                                                   AS CCY
        ,'B'                                                                                                       AS INTERVAL_CODE1       -- 存款余额区间分类
        ,'合计'                                                                                                    AS INTERVAL_DESC1       -- 存款余额区间描述
        ,COALESCE(CASE WHEN DEP.DEPOSIT_TYPE IN ('D011','D013') THEN DEP.ACTUAL_AMOUNT ELSE 0 END,0)               AS CURR_AMOUNT          -- 活期存款余额
        ,COALESCE(CASE WHEN DEP.DEPOSIT_TYPE IN ('D011','D013') THEN DEP.WSUM_AMOUNT   ELSE 0 END,0)               AS CURR_AMOUNT_WSUM     -- 活期存款余额加权值
        ,COALESCE(CASE WHEN DEP.DEPOSIT_TYPE='D03' THEN DEP.ACTUAL_AMOUNT ELSE 0 END,0)                            AS INFORM_AMOUNT        -- 通知存款余额
        ,COALESCE(CASE WHEN DEP.DEPOSIT_TYPE='D03' THEN DEP.WSUM_AMOUNT   ELSE 0 END,0)                            AS INFORM_AMOUNT_WSUM   -- 通知存款余额加权值
        ,COALESCE(CASE WHEN DEP.DEPOSIT_TYPE IN('D061','D062','D063','D064','D065','D066','D067','D068','D069')
                       THEN DEP.ACTUAL_AMOUNT ELSE 0 END,0)                                                        AS ENSURE_AMOUNT        -- 保证金存款余额
        ,COALESCE(CASE WHEN DEP.DEPOSIT_TYPE IN('D061','D062','D063','D064','D065','D066','D067','D068','D069')
                       THEN DEP.WSUM_AMOUNT   ELSE 0 END,0)                                                        AS ENSURE_AMOUNT_WSUM   -- 保证金存款余额加权值
        ,COALESCE(CASE WHEN DEP.DEPOSIT_TYPE IN ('D051','D052') THEN DEP.ACTUAL_AMOUNT ELSE 0 END,0)               AS AGREE_AMOUNT         -- 协定存款余额
        ,COALESCE(CASE WHEN DEP.DEPOSIT_TYPE IN ('D051','D052') THEN DEP.WSUM_AMOUNT   ELSE 0 END,0)               AS AGREE_AMOUNT_WSUM    -- 协定存款余额加权值
        ,COALESCE(CASE WHEN DEP.DEPOSIT_TYPE='D02' THEN DEP.ACTUAL_AMOUNT ELSE 0 END,0)                            AS REG_CURR_AMOUNT      -- 定活两便存款余额
        ,COALESCE(CASE WHEN DEP.DEPOSIT_TYPE='D02' THEN DEP.WSUM_AMOUNT   ELSE 0 END,0)                            AS REG_CURR_AMOUNT_WSUM -- 定活两便存款余额加权值
        ,COALESCE(CASE WHEN DEP.TERM_CODE IN ('1','2') THEN DEP.ACTUAL_AMOUNT ELSE 0 END,0)                        AS AMOUNT_0_3M          -- 存款余额_3月以内
        ,COALESCE(CASE WHEN DEP.TERM_CODE IN ('1','2') THEN DEP.WSUM_AMOUNT   ELSE 0 END,0)                        AS WSUM_0_3M            -- 存款余额加权值_3月以内
        ,COALESCE(CASE WHEN DEP.TERM_CODE ='3' THEN DEP.ACTUAL_AMOUNT ELSE 0 END,0)                                AS AMOUNT_3_6M          -- 存款余额_3到6个月
        ,COALESCE(CASE WHEN DEP.TERM_CODE ='3' THEN DEP.WSUM_AMOUNT   ELSE 0 END,0)                                AS WSUM_3_6M            -- 存款余额加权值_3到6个月
        ,COALESCE(CASE WHEN DEP.TERM_CODE ='4' THEN DEP.ACTUAL_AMOUNT ELSE 0 END,0)                                AS AMOUNT_6_12M         -- 存款余额_6个月到1年
        ,COALESCE(CASE WHEN DEP.TERM_CODE ='4' THEN DEP.WSUM_AMOUNT   ELSE 0 END,0)                                AS WSUM_6_12M           -- 存款余额加权值_6个月到1年
        ,COALESCE(CASE WHEN DEP.TERM_CODE IN ('5','6') THEN DEP.ACTUAL_AMOUNT ELSE 0 END,0)                        AS AMOUNT_1_3Y          -- 存款余额_1到3年
        ,COALESCE(CASE WHEN DEP.TERM_CODE IN ('5','6') THEN DEP.WSUM_AMOUNT   ELSE 0 END,0)                        AS WSUM_1_3Y            -- 存款余额加权值_1到3年
        ,COALESCE(CASE WHEN DEP.TERM_CODE ='7' THEN DEP.ACTUAL_AMOUNT ELSE 0 END,0)                                AS AMOUNT_3_5Y          -- 存款余额_3到5年
        ,COALESCE(CASE WHEN DEP.TERM_CODE ='7' THEN DEP.WSUM_AMOUNT   ELSE 0 END,0)                                AS WSUM_3_5Y            -- 存款余额加权值_3到5年
        ,COALESCE(CASE WHEN DEP.TERM_CODE IN ('8','9') THEN DEP.ACTUAL_AMOUNT ELSE 0 END,0)                        AS AMOUNT_5_999Y        -- 存款余额_5年以上
        ,COALESCE(CASE WHEN DEP.TERM_CODE IN ('8','9') THEN DEP.WSUM_AMOUNT   ELSE 0 END,0)                        AS WSUM_5_999Y          -- 存款余额加权值_5年以上
        ,COALESCE(DEP.ACTUAL_AMOUNT,0)                                                                             AS AMOUNT_ALL           -- 存款余额_所有期限
        ,COALESCE(DEP.WSUM_AMOUNT,0)                                                                               AS WSUM_ALL             -- 存款余额加权值_所有期限
    FROM (SELECT * FROM EDW.DS_DEPB_SUM WHERE DATA_DATE='#V_DATA_DATE#')DEP
    -- 与地区表最细级关联
    LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON DEP.FIN_ORG_NO=REAL.ORG_ID AND '#V_DATA_DATE#' BETWEEN REAL.START_DATE AND REAL.END_DATE
    LEFT JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '#V_DATA_DATE#' BETWEEN AREA.START_DATE AND AREA.END_DATE
    ) T
GROUP BY  T.FIN_ORG_DIST
         ,T.FIN_ORG_NO
         ,T.CCY
         ,T.INTERVAL_CODE1                   -- 存款余额区间分类
         ,T.INTERVAL_DESC1                   -- 存款余额区间描述
         ;